
********************************************************************************
*** Clean Census/CPS Voter Supplement that is used as weights in creating 
*		vote/reg outcomes by race
********************************************************************************

import excel ../raw/census_voter_supplement/table4_summary.xlsx, clear firstrow
ren A group
drop if _n>48 | elec_yr>2010
tab group elec_yr
ren reg_pct reg_pct2
ren vote_pct vote_pct2
gen reg_pct = round((reg_total*100/ total_pop), 0.1)
gen vote_pct = round((vote_total*100/ total_pop), 0.1)
gen pres = elec_yr==2004 | elec_yr==2008 | elec_yr==2012 | elec_yr==2016

foreach out in vote reg {
	sum `out'_pct if group=="other" & pres==1
	global `out'_other_pres = round(r(mean),0.1)
	replace `out'_pct = ${`out'_other_pres} if group=="other" & pres==1
	sum `out'_pct if group=="other" & pres==0
	global `out'_other_mid = round(r(mean),0.1)
	replace `out'_pct = ${`out'_other_mid} if group=="other" & pres==0
}
order elec_yr group reg_pct vote_pct
keep elec_yr group reg_pct vote_pct
sort elec_yr group reg_pct vote_pct
ren reg_pct reg_pct_
ren vote_pct vote_pct_
replace group = substr(group,1,3)
reshape wide reg_pct_ vote_pct_ , i(elec_yr) j(group) string
order elec_yr reg_pct_* vote_pct_*
save ../data_intermediate/reg_vote_pct_2002_2010.dta,replace
